import pymysql
import requests
from lxml import etree

head={
    "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/107.0.0.0 Safari/537.36 Edg/107.0.1418.26"
}

def getoldhooms(url):
    tlist=[]
    rep=requests.get(url,headers=head).text
    html=etree.HTML(rep)
    hoom=html.xpath('/html/body/div[1]/div/div/section/section[3]/section[1]/section[2]/div')
    for web in hoom:
        mylist=[
            web.xpath('./a/div[2]/div[1]/div[1]/h3/text()')[0],
            "".join(web.xpath('./a/div[2]/div[1]/section/div[1]/p[1]/span/text()')),
            str(web.xpath('./a/div[2]/div[1]/section/div[1]/p[2]/text()')[0]).replace(" ","").replace("\n",""),
            web.xpath('./a/div[2]/div[1]/section/div[1]/p[3]/text()')[0],
            str(web.xpath('./a/div[2]/div[1]/section/div[1]/p[4]/text()')[0]).replace(" ","").replace("\n",""),
            str(web.xpath('./a/div[2]/div[1]/section/div[1]/p[5]/text()')[0]).replace(" ","").replace("\n",""),
            web.xpath('./a/div[2]/div[1]/section/div[2]/p[1]/text()')[0],
            "-".join(web.xpath('./a/div[2]/div[1]/section/div[2]/p[2]/span/text()')),
            web.xpath('./a/div[2]/div[1]/div[2]/div/span[1]/text()')[0],
            web.xpath('./a/div[2]/div[1]/div[2]/div/span[2]/text()')[0],
            web.xpath('./a/div[2]/div[1]/div[2]/div/span[3]/text()')[0],
            "".join(web.xpath('./a/div[2]/div[2]/p[1]/span/text()')),
            web.xpath('./a/div[2]/div[2]/p[2]/text()')[0]
        ]
        tlist.append(mylist)
    return tlist

def cunchu(weilist):
    for jun in weilist:
        connect = pymysql.connect(host="localhost", user="root", password="123456", db="spiderwork")
        course = connect.cursor()
        sql="INSERT INTO 安居客二手房 values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"%(jun[0],jun[1],jun[2],jun[3],jun[4],jun[5],jun[6],jun[7],jun[8],jun[9],jun[10],jun[11],jun[12])
        try:
            print("正在写入数据！")
            course.execute(sql)
            connect.commit()
        except Exception as e:
            print("数据写入失败！",e)
            connect.rollback()
            connect.close()

for i in range(3):
    url=f"https://yongzhou.anjuke.com/sale/p{i+1}/?from=HomePage_TopBar"
    weilist=getoldhooms(url)
    cunchu(weilist)